package org.rbreznak.tronco.sql;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Hashtable;


public class DatabaseHandler {

	private Statement statement;
	private ResultSet results;
	private DBConnectionInterface connection;
	
	/**
	 * Create a persistant connection to a MySQL database and setup the 
	 * framework to send queries and make updates
	 * 
	 * @param connection an instance of the connection type (i.e. MySQLConnection)
	 */
	public DatabaseHandler(DBConnectionInterface connection) {
		
		// form the connection then set the class's connection to the custom
		// one that was sent over. This is so that Oracle and other DBS can
		// be used.
		connection.makeConnection();
		Connection conn = connection.getConnection();
		this.connection = connection;
		
		// make make a statement or send an error
		try {
			statement = conn.createStatement();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
		
	/**
	 * execute the given SQL statement. If there is an error the exception will
	 * be printed in the consol
	 * @param sql a sql statement to be executed
	 */
	public void execute(String sql){
		
		// execute the sql statement and store the results in 'results'
		try{
	        //BufferedWriter out = new BufferedWriter(new FileWriter("sql_queries.sql", true));
	        //out.write(sql + "\n");
	        //out.close();
	        
			statement.execute(sql);
			results = statement.getResultSet();
		}catch(Exception e){
			e.printStackTrace();
		}
	}
		
	/**
	 * finds the number of rows in results stored in the connection
	 * @return number of results
	 */
	public int numRows() {
		int rtn=0;
		
		try {
			// go to the last record
			results.last();
			
			// if no records exist then the count is 0
			if(results == null ) {
				return 0;
			}
			
			// get the record number for the last row and store it
			rtn = results.getRow();
			
			// set the cursor at the top again
			results.first();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		// send back the number
		return rtn;
	}
	
	public SQLData getResults() {
		ArrayList<Hashtable> rows   = new ArrayList<Hashtable>();
	
		try {
			
			ResultSetMetaData rsmd = results.getMetaData();
			
			ArrayList<SQLFormat> styles = generateStyles(rsmd, results);
			
			int numRows = numRows();
			int colCount = rsmd.getColumnCount();
			
			for(int i=0;i<numRows;i++) {
				Hashtable<String, String> data = new Hashtable<String, String>();
				
				for(int j = 0; j<colCount;j++) {
					data.put(rsmd.getColumnName(j+1), styles.get(j).getValue()); 
				}
				rows.add(data);
				results.next();
			} 
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return new SQLData().setData(rows);
	}
	
	private ArrayList<SQLFormat> generateStyles(ResultSetMetaData rsmd, ResultSet results) throws SQLException, SQLStyleNotDefined {
		
		ArrayList<SQLFormat> styles = new ArrayList<SQLFormat>();
		
		String colType;
		
		int colNum = (rsmd.getColumnCount());
		
		for(int i=1;i<=colNum;i++) {
						
			colType = (String)rsmd.getColumnTypeName(i);
			
			if(colType.equalsIgnoreCase("INTEGER")) {
				SQLInt sqlint = new SQLInt().setFormat(rsmd.getColumnName(i));
				sqlint.setResults(results);
				styles.add(sqlint);
			} else if(colType.equalsIgnoreCase("VARCHAR")) {
				SQLVarchar sqlvarchar = new SQLVarchar().setFormat(rsmd.getColumnName(i));
				sqlvarchar.setResults(results);
				styles.add(sqlvarchar);
			} else if(colType.equalsIgnoreCase("CHAR")) {
				SQLChar sqlchar = new SQLChar().setFormat(rsmd.getColumnName(i));
				sqlchar.setResults(results);
				styles.add(sqlchar);
			} else if(colType.equalsIgnoreCase("TIMESTAMP")) {
				SQLTimestamp sqltimestamp = new SQLTimestamp().setFormat(rsmd.getColumnName(i));
				sqltimestamp.setResults(results);
				styles.add(sqltimestamp);
			} else {
				throw new SQLStyleNotDefined(colType.toString() + " not a defined type.");
			}
		}
		
		return styles;
	}

	/**
	 * closes the active connection to a database
	 */
	public void close() {
		// kill it
		connection.close();
	}
	
	class SQLStyleNotDefined extends Exception {

		private static final long serialVersionUID = 1L;
		String mistake;

		  public SQLStyleNotDefined()
		  {
		    super();             // call superclass constructor
		    mistake = "unknown";
		  }

		  public SQLStyleNotDefined(String err)
		  {
		    super(err);     // call super class constructor
		    mistake = err;  // save message
		  }
		  
		  public String getError()
		  {
		    return mistake;
		  }

	}
}
